--时效内签收(sign)
--签收时间-应签收时间<1
--签收时间-（寄件时间+时效）<1
--签收时间-寄件时间-时效<1
--签收时间-寄件时间<1+时效

--时效内未签收(unsign)
--统计时间<应签收时间
--统计时间-应签收时间<0
--统计时间-（寄件时间+时效）<0
--统计时间-寄件时间-时效<0
--统计时间-寄件时间<0+时效

--chao时效内未签收(unsign)
--统计时间>=应签收时间
--统计时间-应签收时间>=0
--统计时间-（寄件时间+时效）>=0
--统计时间-寄件时间-时效>=0
--统计时间-寄件时间>=0+时效

--chao时效签收(sign)
--签收时间-应签收时间>=1
--签收时间-（寄件时间+时效）>=1
--签收时间-寄件时间>=1+时效

with detail_table as (--做去重操作,确保运单唯一
    select code                                                  --客户code
         , name                                                  --客户name
         , waybill_no                                            --运单号
         , order_id                                              --订单编号
         , date_time                                             --日期
         , max(cancel_flag)            as cancel_flag            --订单取消1代表取消,0代表未取消
         , max(pick_flag)              as pick_flag              --取件标识1取件,0代表未取件
         , max(sign_falg)              as sign_falg              --签收标识1正常签收和退件签收 0未签收
         , max(7d_update_flag)         as 7d_update_flag         --超过7天未更新
         , max(actual_taking_join)     as actual_taking_join     --实际揽收单标识 1是,0 否
         , max(del_war_join)           as del_war_join           --派件中标识,1派件中,0 未派件中
         , max(intercept_join)         as intercept_join         --拦截件标识1代表拦截件,0代表非拦截件
         , max(difficute_join)         as difficute_join         --问题件标识,1问题件,0不是问题件
         , max(work_order_join)        as work_order_join        --客诉标识,1代表客诉0代表非客诉
         , max(arbitration_join)       as arbitration_join       --丢失标识 ,1代表丢失,0代表未丢失
         , max(express_join)           as express_join           --破损标识 1,代表破损,0未破损
         , max(xujia_sign_join)        as xujia_sign_join        --虚假签收标识 1,代表虚假签收,0未虚假签收
         , max(diff_3input_time_flag)  as diff_3input_time_flag  --1代表订单创建时间大于3天的标识
         , max(diff_10input_time_flag) as diff_10input_time_flag --1代表订单创建时间大于10天的标识
         , max(wph_pro_name_flag)      as wph_pro_name_flag      --1代表项目名是唯品会 0代表其他
         , max(pick_time_flag)         as pick_time_flag         --1代表项目名是唯品会 0代表其他
         , max(diff_day)               as diff_day               --时效时间差
         , max(need_city_t_timely)     as need_city_t_timely     --时效天数
    from jms_dm.dm_mng_customer_daliy_waybill_detail_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -44)
      and dt <= '{{ execution_date | cst_ds }}'
      and code is not null
    group by code       --客户code
           , name       --客户name
           , waybill_no --运单号
           , order_id   --订单编号
           , date_time --日期
),
     on_taking_detail_table as (--做去重操作,确保运单唯一
         select code                                               --客户code
              , name                                               --客户name
              , input_time_6hour                  as date_time     --日期
              , sum(if(on_taking_flag = 1, 1, 0)) as on_taking_sum --准点揽收量
              , count(1)                          as actual_taking_num_6hour
         from (
                  select code       --客户code
                       , name       --客户name
                       , waybill_no --运单号
                       , max(on_taking_flag) as on_taking_flag
                       , input_time_6hour
                  from jms_dm.dm_mng_customer_daliy_waybill_taking_detail_dt
                  where dt >= date_add('{{ execution_date | cst_ds }}', -44)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and code is not null
                  group by code       --客户code
                         , name       --客户name
                         , waybill_no --运单号
                         , input_time_6hour
              ) a
         group by code --客户code
                , name --客户name
                , input_time_6hour --日期

     ),
     wide_tab as (
         select code                                                                     --客户code
              , max(name)                                    as name                     --客户name
              , date_time
              , sum(if(have_taking_no_send_flag = 1, 1, 0))  as have_taking_no_send_num  --有揽未发量
              , sum(if(have_send_no_arrival_flag = 1, 1, 0)) as have_send_no_arrival_num --有发未到量
         from jms_dm.dm_mng_customer_daliy_waybill_tab_detail_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -44)
           and dt <= '{{ execution_date | cst_ds }}'
           and code is not null
         group by code--客户code
                , date_time
     )
insert
overwrite
table
jms_dm.dm_mng_customer_daliy_customer_summary_dt
partition
(
dt
)
select t2.project_name                                             as project_name             --项目名称
     , t1.code                                                     as code                     --客户code
     , t2.customer_name                                            as name                     --客户name
     , t2.customer_mall_id                                         as mall_id                  --商家id（主要对接第三方客户）
     , t2.customer_mall_name                                       as mall_name                --商家名称
     , t2.customer_network_id                                      as customer_network_id      --客户所属网点
     , t2.customer_network_code                                    as customer_network_code    --客户所属网点
     , t2.customer_network_name                                    as customer_network_name    --客户所属网点
     , t1.date_time                                                as date_time                --日期
     , sum(waybill_total)                                          as waybill_total            --总订单量
     , sum(cancel_num)                                             as cancel_num               --总取消量
     , (sum(waybill_total) - sum(cancel_num))                      as need_taking_num          --应揽收量
     , sum(actual_taking_num)                                      as actual_taking_num        --实际揽收量
     , sum(no_taking_num)                                          as no_taking_num            --有单未揽收量
     , (sum(actual_taking_num) - sum(pick_num))                    as lack_taking_num          --有单漏揽收量
     , (sum(actual_taking_num) - sum(sign_num))                    as transport_num            --运输中量
     , sum(del_war_num)                                            as del_war_num              --派件中量
     , (sum(actual_taking_num) - sum(no_tuotou))                   as no_safe_num              --未妥投量
     , sum(sign_num)                                               as sign_num                 --已签收量
     , sum(intercept_num)                                          as intercept_num            --拦截量
     , sum(success_intercept_num)                                  as success_intercept_num    --成功拦截量
     , sum(7d_update_intercept_num)                                as 7d_update_intercept_num  --轨迹断更量
     , sum(work_order_num)                                         as work_order_num           --客诉量
     , sum(arbitration_num)                                        as arbitration_num          --丢失量
     , sum(express_num)                                            as express_num              --破损量
     , sum(xj_sign)                                                as xj_sign                  --虚假签收量
     , sum(nvl(sign_in_num, 0))                                    as sign_in_num              --时效内签收量
     , sum(nvl(sign_out_num, 0))                                   as sign_out_num             --超时效签收量
     , sum(nvl(nosign_in_num, 0))                                  as nosign_in_num            --时效内未签收量
     , sum(nvl(nosign_out_num, 0))                                 as nosign_out_num           --超时效内未签收量
     , round(sum(nvl(sign_in_num, 0)) / sum(actual_taking_num), 6) as sign_rate                --时效达成率
     , sum(on_taking_sum)                                          as on_taking_sum            --准点揽收量
     , sum(nvl(have_taking_no_send_num, 0))                        as have_taking_no_send_num  --有揽未发量
     , sum(nvl(have_send_no_arrival_num, 0))                       as have_send_no_arrival_num --有发未到量
     , customer_agent_code                                         as customer_agent_code      --客戶所属代理区code
     , customer_agent_name                                         as customer_agent_name      --客戶所属代理区name
     , sum(nvl(actual_taking_num_6hour, 0))                        as actual_taking_num_6hour  --新实际揽收量
     , t1.date_time                                                as dt
from (
         select code                                                                                                      --客户code
              , date_time                                                                                                 --日期
              , count(order_id)                                                               as waybill_total            --总订单量
              , sum(cancel_flag)                                                              as cancel_num               --总取消量
              , sum(actual_taking_join)                                                       as actual_taking_num        --实际揽收量
              , sum(if(actual_taking_join = 1 and pick_flag = 1, 1, 0))                       as pick_num                 --有取件时间量
              , sum(if(actual_taking_join = 1 and sign_falg = 1, 1, 0))                       as sign_num                 --已签收和正常签收量
              , sum(if(actual_taking_join = 1 and del_war_join = 1 and sign_falg = 0, 1, 0))  as del_war_num              --派件签收量
              , sum(if(actual_taking_join = 1 and intercept_join = 1, 1, 0))                  as intercept_num            --拦截量
              , sum(
                 if(actual_taking_join = 1 and intercept_join = 1 and sign_falg = 1, 1, 0))   as success_intercept_num    --拦截成功量
              , sum(
                 if(actual_taking_join = 1 and intercept_join = 0 and difficute_join = 0 and 7d_update_flag = 1 and
                    sign_falg = 0, 1,
                    0))                                                                       as 7d_update_intercept_num  --轨迹断更量
              , sum(if(actual_taking_join = 1 and work_order_join = 1, 1, 0))                 as work_order_num           --客诉量
              , sum(if(actual_taking_join = 1 and arbitration_join = 1, 1, 0))                as arbitration_num          --丢失量
              , sum(if(actual_taking_join = 1 and express_join = 1, 1, 0))                    as express_num              --破损量
              , sum(if(actual_taking_join = 1 and (sign_falg = 1 or del_war_join = 1), 1, 0)) as no_tuotou                --妥投量
              , sum(if(actual_taking_join = 1 and xujia_sign_join = 1, 1, 0))                 as xj_sign                  --虚假签收量
              , sum(case
                        when cancel_flag <> 1 and actual_taking_join <> 1
                            then 1
                        else 0 end)                                                           as no_taking_num            --有单未揽
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(
                            if(actual_taking_join = 1 and sign_falg = 1 and diff_day - need_city_t_timely < 1,
                               1, 0))
             end                                                                              as sign_in_num              --时效内签收量
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(
                            if(actual_taking_join = 1 and sign_falg = 1 and diff_day - need_city_t_timely >= 1, 1,
                               0))
             end                                                                              as sign_out_num             --超时效签收量
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(if(actual_taking_join = 1 and sign_falg = 0 and diff_day - need_city_t_timely < 0, 1,
                                0))
             end                                                                              as nosign_in_num            --时效内未签收量
              , case
                    when max(need_city_t_timely) is null then null
                    else sum(
                            if(actual_taking_join = 1 and sign_falg = 0 and diff_day - need_city_t_timely >= 1, 1, 0))
             end                                                                              as nosign_out_num           --超时效内未签收量
              , 0                                                                             as on_taking_sum
              , 0                                                                             as have_taking_no_send_num  --有揽未发量
              , 0                                                                             as have_send_no_arrival_num --有发未到量
              , 0                                                                             as actual_taking_num_6hour  --新实际揽收量
         from detail_table
         group by code      --客户code
                , date_time --日期
         union all
         select code                         --客户code
              , date_time                    --日期
              , 0 as waybill_total           --总订单量
              , 0 as cancel_num              --总取消量
              , 0 as actual_taking_num       --实际揽收量
              , 0 as pick_num                --有取件时间量
              , 0 as sign_num                --已签收和正常签收量
              , 0 as del_war_num             --派件签收量
              , 0 as intercept_num           --拦截量
              , 0 as success_intercept_num   --拦截成功量
              , 0 as 7d_update_intercept_num --轨迹断更量
              , 0 as work_order_num          --客诉量
              , 0 as arbitration_num         --丢失量
              , 0 as express_num             --破损量
              , 0 as no_tuotou               --妥投量
              , 0 as xj_sign                 --虚假签收量
              , 0 as no_taking_num           --有单未来
              , 0 as sign_in_num             --时效内签收量
              , 0 as sign_out_num            --超时效签收量
              , 0 as nosign_in_num           --时效内未签收量
              , 0 as nosign_out_num          --超时效内未签收量
              , on_taking_sum
              , 0 as have_taking_no_send_num
              , 0 as have_send_no_arrival_num
              , actual_taking_num_6hour      --新实际揽收量
         from on_taking_detail_table
         union all
         select code                         --客户code
              , date_time                    --日期
              , 0 as waybill_total           --总订单量
              , 0 as cancel_num              --总取消量
              , 0 as actual_taking_num       --实际揽收量
              , 0 as pick_num                --有取件时间量
              , 0 as sign_num                --已签收和正常签收量
              , 0 as del_war_num             --派件签收量
              , 0 as intercept_num           --拦截量
              , 0 as success_intercept_num   --拦截成功量
              , 0 as 7d_update_intercept_num --轨迹断更量
              , 0 as work_order_num          --客诉量
              , 0 as arbitration_num         --丢失量
              , 0 as express_num             --破损量
              , 0 as no_tuotou               --妥投量
              , 0 as xj_sign                 --虚假签收量
              , 0 as no_taking_num           --有单未来
              , 0 as sign_in_num             --时效内签收量
              , 0 as sign_out_num            --超时效签收量
              , 0 as nosign_in_num           --时效内未签收量
              , 0 as nosign_out_num          --超时效内未签收量
              , 0 as on_taking_sum           --准点揽收量
              , have_taking_no_send_num      --有揽未发量
              , have_send_no_arrival_num     --有发未到量
              , 0 as actual_taking_num_6hour --新实际揽收量
         from wide_tab
     ) t1
         left join
     (
         select a.project_name      as project_name          --项目名称
              , a.code              as customer_code         --客户编码
              , a.name              as customer_name         --客户名称
              , a.mall_id           as customer_mall_id      --商家id（主要对接第三方客户）
              , a.mall_name         as customer_mall_name    --商家名称
              , a.customer_category as customer_category     --客户类别（1-终端寄件，2-农特专递，3-总部共享，4-代理区共享）
              , b.id                as customer_network_id   --客户所属网点id
              , b.code              as customer_network_code --客户所属网点code
              , b.name              as customer_network_name --客户所属网点name
              , b.agent_code        as customer_agent_code   --客户所属代理区
              , b.agent_name        as customer_agent_name   --客户所属代理区
         from (
                  select *
                       , row_number() over (partition by code order by flag desc,update_time desc ) as rn
                  from (
                           select project_name --项目名称
                                , id           --客户id
                                , code         --客户编码
                                , name         --客户名称
                                , network_id   --客户所属网点
                                , mall_id      --商家id（主要对接第三方客户）
                                , mall_name    --商家名称
                                , update_time
                                , 6 as customer_category
                                , 2 as flag
                           from jms_dim.dim_lmdm_sys_customer
                           where code is not null
                           union all
                           select concat('菜鸟vip-', name) as project_name
                                , id                     as id
                                , code                   as code
                                , name                   as name
                                , null                   as network_id
                                , null                   as mall_id           --商家id（主要对接第三方客户）
                                , null                   as mall_name         --商家名称
                                , update_time            as update_time
                                , customer_category      as customer_category --客户类别（1-终端寄件，2-农特专递，3-总部共享，4-代理区共享）
                                , 1                      as flag
                           from jms_dim.dim_sys_customer_additional_base a
                           where code is not null
                             and a.name <> 'test'
                       ) a
              ) a
                  left join jms_dim.dim_network_whole_massage b
                            on a.network_id = b.id
         where rn = 1
           and a.project_name is not null
           and a.code is not null
           and a.customer_category = 6
     ) t2
     on t1.code = t2.customer_code
group by t2.project_name
       , t1.code
       , t2.customer_name
       , t2.customer_mall_id
       , t2.customer_mall_name
       , t2.customer_network_id
       , t2.customer_network_code
       , t2.customer_network_name
       , t2.customer_agent_code
       , t2.customer_agent_name
       , t1.date_time
    distribute by dt
;
